Working with Form Based Applications in vb.net

 

Form1:

Text: VB270 SQL Client Tool

Back color: blue

Go to project [menu] à Add a reference

Click on the .NET tab

Double click on the Execute button

Above formal class write

Imports System.Data

Imports System.Data.SqlClient

// Code for button click execute

‘Execute (button_Click)

Try

Dim cn as new SqlConnection

(“userid=sa; password = 123(changes from system to system); database = master; data source = sekhar”) (server name changes from system 2 system)

Dim stmt as String

Stmt = Mid (txtstmt.Text, 1, txtStmt.Text.IdexOf (“ “))

If stmt.ToUpper = ‘SELECT then

Dim da as new sqlDataAdapter (txtstmt.Text, cn)

Dim ds as new DataSet

Da.Fill (ds, “tmpTable”)

dgvResults.DataSource = ds.Tables (0)or (temptable)

lb1 Result.Text = ds.Tables (0).Rows.Count & “Rows selected”

Else

Dim cmd as new SqlCommand

cmd.Connection = cn

cmd.CommandType = CommandType.Text

cmd.CommandText = txtStmt.Text

cn.Open ()

cmd.Execute NonQuery ()

lb1Result.Text = Stmt.ToUpper & “Statement Executed”

End if

Catch ex as Exception

Lb1Result.text = “Error:” & ex.Message

Finally

lstHistory.Items.Add (txtStmt.Text)

End try

Code for clear button

‘Clear (button_Click)

txtStmt.Clear ()

lb1Result.Text = String.Empty

dgvResults.DataSource = Nothing

txtStmt.Focus ()

// Code for clear History button

‘ClearHistory (button_Click)

ListHistory.Items.Clear ()

For close button_click

‘Close (button_click)

End

Double click on List Box Control

‘(ListBox)listHistory – selected Index changed

txtStmt.Text = ListHistory.SelectedItem  

For Form:

Accept Button: btnExecute (if pressed enter)

Cancel Button: btnClose (if pressed esc)  

Execute:

Working with Add.Net disconnected model:  

Performing navigations using disconnected model:

Binding Manager Base:

It is a class which provides the members for supporting the data navigations and manipulations.  

Properties to support navigation:

Position:

• It is used to specify the current ROW referred to by the Binding Manager base Variable. For Ex: bmb.position = 0
• The above statement refers to the first row present at the data member bound with the BMB variable.  

Count:

Returns the number of rows present at the data member of the data set.  

Binding Content:

It is used to assign the data member of the data set to the BMB variable. We have some tables called categories; it is in the SQL server north wind database.

 

 

In public class Form1

‘Form declarations . . . .

Dim cn as SqlConnection

Dim da as SqlDataAdapter

Dim ds as DataSet

Dim bmb as BindingManagerBase

In public class sub form1 . .  .

‘Form_load

Cn = New SqlConnection (“user id = sa; password = ___ 123; database = northwind; data source = sekhar(server name differs)”)

Da = New SqlDataAdapter (“Select * from categories”, cn)

Ds = New DataSet

Da.Fill (ds, “Categories”)

Bmb = Binding Context (ds.tables (“Categories”))

Bmb.Position = 0

Code under ‘Form declerations

Public sub ShowCategory (By Val index as integer)

txtCid.Text = ds.Tables (“categories”).Rows(index) (“Category Id”)

txtCname.Text = ds.Tables (“categories”).Rows(index) (“Category Name”)

txtDesc.Text = ds.Tables (“categories”).Rows(index) (“Description”)

For First button1_clcik

‘First (button_click)

Bmb.Position = 0

ShowCategory (bmb.position)

// Code for previous button

‘Prev (button_click)

Bmb.position - = 1

ShowCategory (bmb.position)

“Code for next button_click

‘Next (button_click)

Bmb.position+ = 1

ShowCategory (bmb.position)

“Code for Last button_click

‘Last (button_click)

Bmb.position = bmb.count -1

ShowCategory (bmb.position)  

Observation

 

Attaching Database for SQL Server 2005:

Download “SQL 2000 Sample Db.msi” from the internet Install the above file.  

Note:

The above step will create a folder in C:drive with the name “SQL Server 2000 sample Databases” and copies the sample Databases to the folder.

Open SQL Server Management Studio

Start  Run   SQL Wb

Connect to the “Databases” present at the object Explorer [F8] and click on “Attach”

• Click on Add and select Northwind.mdf file from the created folder.
• Click on OK.

 

Various Methods to search the data using ADD.Net Disconnected Model:

Contains: It accepts the primary key value and returns true if the record is existing else returns false  

Syntax: DataSetName.DataTable.Rows.Contains(PrimaryKeyColumnValue)  

Find: It is used to return a data row if the record exists by accepting the PrimaryKeyColumnValue.

Note: If the record is not existing for the provided value then this method returns nothing where “nothing” is a keyword.

Let us have a table

 

(Code for search (button_click))

Imports System.Data

Imports System.Data.SqlClient

Above search button_click in Form2

‘Form declarations . . . .

Dim cn as SqlConnection

Dim ds as SqlDataAdapter

Dim ds as DataSet

Double click on form code for form_lead

Cn = New Sql Connection (“user Id = sa; password = 123; database = north wind; data source = Sekhar”)

da = New Sql Data Adapter (“Select * from products”, cn)

ds = New DataSet

da.Fill (ds, “products”)

Here we are in search of pid so we have à to put primary constructor to the column.

ds.Tables (“Products”).Constraints.Add (“ProductId_Pk”,                                    ds.Tables

(“Products”).Columns (Product Id), true)

Code for search button_click

‘Dim pid as Integer

Pid = Val (txtProductId.Text)

If ds.Tables (“Products”).Rows.Contains(pid) = True

Then

Dim row as Integer

Row = ds.Tables (“Products”).Rows.Find (pid)

txtProductName.Text = row (“Product Name”)

txtQuantity.Text = row (“Quantity per Unit”)

txtPrice.Text = row (“UnitPrice”)

Else

MessageBox.Show (“NoProductFound”, Me.Text)

txtProductName.Clear ()

txtQuantityName.Clear ()

txtPrice.Clear ()

txtProductId.Focus ()

// Code for close button_click

End

Execute  

Observation:

     

Find:

It accepts a primary key column value and returns the row index value if the record exists else the method returns -1.  

Note:

In order to use the above method, it is mandatory that the data should be sorted based on the primary key column value.    

Syntax:

DataViewName.Find (PrimaryKeyColumnValue)

• When the form is loaded all the products must be shown and if you enter a specific product Id that must be highlighted.
• These are the requirements.
• Project [menu] à Add New Item à WINForm

Sample design

   

Code for Form3

Above the Imports System.Data

Imports System.Data.Sqlclient

In Form3

Dim cn as SqlConnection

Dim da as SqlDataAdapter

Dim ds as DataSet

Dim dv as DataView

Dim tmpindex as Integer

In Form_Load

Cn = New SqlConnection (“Userid = sa; password = 123; database = north wind; data source = sekhar”)

da = New Sql Data Adapter (“Select from products”, cn)

ds = New DataSet

da.Fill (ds, “products”)

ds.Tables (“Products”).Constraints.Add (“ProductId_Pk”,   ds.Tables (“Products”).Columns (Product Id), true)

dv = New DataView (ds.Tables (“Products”))

dv.Sort = “Product Id”

dgv.ProductsData.DataSource = dv

// Code for “Advanced Search” (button_click)

dvProductsData.Rows (tmpIndex).Selected = False

If ds.Tables (“Products”).Rows.Contains

(txtProductId.Text) = true

Then

Dim index as Integer

Index = dv.Find (txtProductId.Text)

dgvProductsData.Rows(index).Selected = true    To highlight

tmpIndex = index    the item (row)

Else

MessageBox.Show (“NoProductFound”, Me.Text)

txtProduct Id.Focus ()

End if

// Code for close

End

 Execute  

Row Filter:

It is used to filter the data based on the condition.

Syntax:

DataViewName.RowFilter = condition;

 

Take new Form

• Place flow Layout panel on form and set the property Dock = Top
• Place GroupBox on Form and set the property Dock = Top
• Place a label, textbox, button1, Button2 in the Groupbox

For Label: Text = ProductName

TextBox1: Id = txtProductName

Button1: Text = Reset        ID = btn Reset

Button2: Text = close        ID = btn Close

• Place DataGridViewControl below Groupbox and set Dock = Fill

For form text = Product Details

In Form4_Load (above)

Imports System.Data

Imports System.Data.SqlClient

In public class Form4

‘Form Declarations

Dim cn as SqlConnection;

Dim da as DataAdapter;

Dim ds as DataSet

Dim dv as DataView

// Form_Load

‘Form_Load

FlowLayoutpanel1.Controls.Clear ()

For i as Integer = 65 to 90

Dim ll .as New LinkLabel

ll.Text = chr (i)         --- To return character or integer value

Add Handler ll.click, address of ll_click à to get to the link of the label by click event.

FlowLayoutPanel l.Controls.Add (ll)

Next

Cn = New SqlConnection (“userid=sa, password=123, database = northwind, data source = “sekhar”)

Da = New SqlDataAdapter (“Select * from products”, cn)

Ds = New DataSet

Ds.Fill (ds, “Products)

Dv = New DataView (ds.Tables (“Products”))

DataGridView1.DataSource = dv  

In form declarations

Private sub ll_click (by Val sender as object, By Val e as Event Args)

Dim as New LinkLabel                               See Dynamic Event Handling

l = CType (Sender, LinkLabel)                 Program back

dv.RowFilter = “ProductName like ““ &1.Text&”%”

// Double click on TextBox

‘txtProductName.Text changed

dv.RowFilter = String.Format (“ProductName like “{0}%””, txtProductName.Text)

// Double click on Reset                           For close btn

‘Reset (button1_click)                              End

dv.RowFilter = “ ”  

Select:

It is used to search the data based on the Non Primary Key column i.e; based on a condition.  

Note: Select the method of the dataset will return a collection of data rows if the record exists.

Syntax:

DataSetName.DataTable.Select (condition

 

In code write Imports System.data

Imports System.Data.SqlClient

In Form5 code

‘Form declarations

Dim cn as SqlConnection

Dim da as SqlDataAdapter

Dim ds as DataSet

For Form5_Load

Cn = New SqlConnection (“userid=sa, password=123, database = northwind, data source = “sekhar”)

Da = New SqlDataAdapter (“Select * from products”, cn)

Ds = New DataSet

Ds.Fill (ds, “Products)

For show Products (button1_click)

‘Show products (button_click)

ListProducts.Items.Clear ()

Dim rows () as DataRow

rows = ds.Tables (“Product”).Select (“CategoryId =” & txtCategoryId.Text)

If rows.Length > 0 Then

For i as Integer = 0 to rows.length -1

ListProducts.Items.Add (rows(i) (“ProductName”)

Else

MessageBox.Show (“No Data found”, Me.Text)

End If

For close btn

End

Execute ()

 Consuming the .NET assembly from Windows Forms application

• Select Windows Form template from VS.Net
• Design the form as per the requirement

 

Add the reference of the assembly project [menu] Add reference  Browse Bank270Transactions.dll

Write code as per Real.

Imports Bank270Transactions

For button1_click

‘Transfer (button_click)

Dim obj as New transactions

If obj.Transfer (txtfromacno.Text, txtToAcno.Text; txtAmount.Text) = True

Then

MessageBoxShow (“Amount Transferred”, Me.Text)

Else

MessageBoxShow (“error while transferring amount”, Me.Text)

End if

When we execute and hit transfer error occurs since we used delay signed assembly and it allows us to use but not to debug or execute. So we have to add a verification entry. Go to .Net Command Prompt

Sn – vr   give complete path of assembly.  

For an in-depth knowledge, click on below

• VB.Net Interview Questions
• Windows Forms Based Application in vb.net
• Working with typed Dataset in vb.net